package com.ruicar.afs.cloud.batch.dayend.process;

import com.alibaba.fastjson.JSONObject;
import com.ruicar.afs.cloud.batch.dayend.handle.run.SystemJdbcConfig;
import com.ruicar.afs.cloud.batch.frame.AbsBatchRunner;
import com.ruicar.afs.cloud.common.core.constant.CommonConstants;
import com.ruicar.afs.cloud.common.core.enums.AfsEnumUtil;
import com.ruicar.afs.cloud.common.core.exception.AfsBaseException;
import com.ruicar.afs.cloud.common.core.uid.UidGenerator;
import com.ruicar.afs.cloud.common.core.util.SpringContextHolder;
import com.ruicar.afs.cloud.common.modules.constant.VoucherBuriedPointNo;
import com.ruicar.afs.cloud.common.modules.contract.enums.*;
import com.ruicar.afs.cloud.common.util.EmptyUtils;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * <p>Description: 未分配金额处理任务</p>
 *
 * @author ZC.GUO
 * @version 1.0
 * @date create on 2020-08-27 15:38
 */
public class HandleUnusedFundsBatchRunner extends AbsBatchRunner {

    private UidGenerator uidGenerator = SpringContextHolder.getBean(UidGenerator.class);
    private SystemJdbcConfig systemJdbcConfig= SpringContextHolder.getBean(SystemJdbcConfig.class);
    @Override
    public void process(String param) {
        Connection base = this.getConnection();
        String breakPointString = loadBreakPoint();
        Connection contract = this.getConnection(systemJdbcConfig.getBasic());
        Connection finance = this.getConnection(systemJdbcConfig.getFinance());
        Connection manage = this.getConnection(systemJdbcConfig.getManage());
        Connection account = this.getConnection(systemJdbcConfig.getAccount());
        try {
            /**  获取需要处理的虚拟账户 by ZC.GUO  **/
            List<Long> idList = this.getVirtualAccountId(finance, breakPointString);
            /***批量日期**/
            Date batchDate = new Date(batchDateInfo.getBatchDate().getTime());
            /**  循环对虚拟账户的未分配金额进行处理 by ZC.GUO  **/
            if (EmptyUtils.isNotEmpty(idList)) {
                for (Long virtualAccountId : idList) {
                    this.takeVirtualAccount(virtualAccountId, finance, contract, manage,account, breakPointString,batchDate);
                }
            }
        } catch (Exception e) {
            logger.error("", e);
            throw new AfsBaseException(e.getMessage());
        } finally {
            clean(contract);
            clean(finance);
            clean(base);
        }
    }

    /**
     * 处理虚拟账户的所有未分配记录
     *
     * @param
     * @return
     * @author ZC.GUO
     */
    public void takeVirtualAccount(Long virtualAccountId, Connection finance, Connection contract, Connection manage,Connection account,String breakPointString,Date batchDate) {
        /**  断点 by ZC.GUO  **/
        JSONObject breakPoint = null;
        if (EmptyUtils.isNotEmpty(breakPointString)) {
            breakPoint = JSONObject.parseObject(breakPointString);
        }
        /** 获取未分配金额的id by ZC.GUO  **/
        StringBuffer querySql = new StringBuffer();
        querySql.append("select id,fund_id,revision,amount from finance_cust_unused_funds fund where fund.account_id = ? and fund.funds_type = 'surplus' and fund.`status` = 'mayuse' and fund.amount > 0");

        /**  添加断点 by ZC.GUO  **/
        if (EmptyUtils.isNotEmpty(breakPoint)) {
            String unusedId = breakPoint.getString("unusedId");
            querySql.append(" and fund.id > " + unusedId);
        }

        querySql.append(" order by fund.id asc ");


        PreparedStatement queryPs = null;
        ResultSet queryRs = null;
        try {
            queryPs = finance.prepareStatement(querySql.toString());
            queryPs.setLong(1, virtualAccountId);
            queryRs = queryPs.executeQuery();
            while (EmptyUtils.isNotEmpty(queryRs) && queryRs.next()) {
                Long unusedId = queryRs.getLong("id");
                Long receiptId = queryRs.getLong("fund_id");
                BigDecimal amount = queryRs.getBigDecimal("amount");

                /**  处理收据的未分配金额 by ZC.GUO  **/
                this.takeReceiptRecord(virtualAccountId, unusedId, receiptId, amount, finance, contract, manage,account,batchDate);

            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        } finally {
            clean(queryPs);
            clean(queryRs);
        }
    }

    public String buildBreakPoint(Long virtualAccountId, Long unusedId) {
        JSONObject breakPoint = new JSONObject();
        breakPoint.put("virtualAccountId", virtualAccountId.toString());
        breakPoint.put("unusedId", unusedId.toString());
        return breakPoint.toJSONString();
    }

    /**
     * 对收据的未分配金额进行处理
     *
     * @param
     * @return
     * @author ZC.GUO
     */
    public void takeReceiptRecord(Long virtualAccountId, Long unusedId, Long receiptId, BigDecimal amount, Connection finance, Connection contract, Connection manage, Connection account,Date batchDate) {
        /** 获取借据信息id by ZC.GUO  **/
        StringBuffer queryOverdueSql = new StringBuffer();
        queryOverdueSql.append("SELECT debit.current_absolute_overdue ");
        queryOverdueSql.append(" FROM basic_debit_info debit   ");
        queryOverdueSql.append(" WHERE debit.contract_no = ? and debit.del_flag = '0' ");
        PreparedStatement queryOverduePs = null;
        ResultSet queryOverdueRs = null;

        /** 获取收据信息id by ZC.GUO  **/
        StringBuffer querySql = new StringBuffer();
        querySql.append("SELECT receipt.write_target,receipt.source_type, receipt.used_amt, receipt.unused_amt, receipt.contract_no,receipt.source_account,receipt.credit_account");
        querySql.append(" ,info.business_type,info.channel_full_name FROM basic_receipt_record receipt inner join  basic_main_info info on receipt.contract_no =info.contract_no");
        querySql.append(" WHERE receipt.id = ? and receipt.del_flag = '0' ");
        PreparedStatement queryPs = null;
        ResultSet queryRs = null;

        /**  是否LPR判断 by ZC.GUO  **/
        StringBuffer lprQuerySql = new StringBuffer();
        lprQuerySql.append("SELECT prm.atom_value");
        lprQuerySql.append(" FROM basic_financial_agreement agr");
        lprQuerySql.append(" LEFT JOIN basic_product_param prm ON agr.product_id = prm.product_id");
        lprQuerySql.append(" AND agr.contract_no = prm.contract_no");
        lprQuerySql.append(" AND prm.atom_key = 'rateType'");
        lprQuerySql.append(" AND agr.cost_type = 'CAR_AMT'");
        lprQuerySql.append(" WHERE agr.contract_no = ? ");
        PreparedStatement lprQueryPs = null;
        ResultSet lprQueryRs = null;
        try {

            queryPs = contract.prepareStatement(querySql.toString());
            queryPs.setLong(1, receiptId);
            queryRs = queryPs.executeQuery();
            if (EmptyUtils.isNotEmpty(queryRs) && queryRs.next()) {
                String writeTarget = queryRs.getString("write_target");
                BigDecimal usedAmt = queryRs.getBigDecimal("used_amt");
                BigDecimal unusedAmt = queryRs.getBigDecimal("unused_amt");
                String contractNo = queryRs.getString("contract_no");
                String sourceAccount = queryRs.getString("source_account");
                String creditAccount = queryRs.getString("credit_account");
                String sourceType = queryRs.getString("source_type");
                String businessType = queryRs.getString("business_type");
                String channelFullName = queryRs.getString("channel_full_name");
                if (WriteTargetTypeEnum.beforeCharge.toString().equals(writeTarget)) {
                    /**  提前还款收据处理 by ZC.GUO  **/
                    this.prepaymentReceiptHandle(sourceType, virtualAccountId, unusedId, receiptId, usedAmt, unusedAmt, contractNo, sourceAccount, creditAccount, contract, finance, manage,account,batchDate,businessType,channelFullName);
                } else {
                    boolean isLpr = true;
                    lprQueryPs = contract.prepareStatement(lprQuerySql.toString());
                    lprQueryPs.setString(1, contractNo);
                    lprQueryRs = lprQueryPs.executeQuery();
                    if (EmptyUtils.isNotEmpty(queryRs) && queryRs.next()) {
                        String atomValue = lprQueryRs.getString("atom_value");
                        isLpr = CommonConstants.COMMON_YES.equals(atomValue);
                    }
                    queryOverduePs = contract.prepareStatement(queryOverdueSql.toString());
                    queryOverduePs.setString(1, contractNo);
                    queryOverdueRs = queryOverduePs.executeQuery();
                    int overdueDays = 0;
                    if (EmptyUtils.isNotEmpty(queryOverdueRs) && queryOverdueRs.next()) {
                        overdueDays = queryOverdueRs.getInt("current_absolute_overdue");
                    }
                    /**  正常还款收据处理 by ZC.GUO  **/
                    this.normalReceiptHandle(sourceType, virtualAccountId, unusedId, receiptId, usedAmt, unusedAmt, contractNo, sourceAccount, creditAccount, contract, finance, account,isLpr, overdueDays,batchDate,businessType,channelFullName);
                }
            } else {
                /**  收据不存在 by ZC.GUO  **/
                this.takeFinanceForReceiptNotFind(virtualAccountId, unusedId, amount, finance);
            }
            /**  保存断点 by ZC.GUO  **/
            saveBreakPoint(this.buildBreakPoint(virtualAccountId, unusedId));
            account.commit();
            contract.commit();
            finance.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        } finally {
            clean(queryPs);
            clean(queryRs);
            clean(queryOverduePs);
            clean(queryOverdueRs);
            clean(lprQueryPs);
            clean(lprQueryRs);
        }
    }

    /**
     * 提前还款核销
     *
     * @param
     * @return
     * @author ZC.GUO
     */
    public void prepaymentReceiptHandle(String sourceType, Long virtualAccountId, Long unusedId, Long receiptId, BigDecimal usedAmt, BigDecimal unusedAmt, String contractNo, String sourceAccount, String creditAccount, Connection contract, Connection finance, Connection manage,Connection account,Date batchDate,String businessType,String channelFullName) {
        /**  业务操作id by ZC.GUO  **/
        Long transLineId = uidGenerator.getUID();
        /**  来款操作id by ZC.GUO  **/
        Long receiptOperateId = uidGenerator.getUID();
        /**  最终处理金额 by ZC.GUO  **/
        BigDecimal handleAmount = BigDecimal.ZERO;
        /**  费用核销（不包括提前结清费用） by ZC.GUO  **/
        handleAmount = handleAmount.add(this.normalCostHandle(receiptOperateId, virtualAccountId, unusedId, receiptId, usedAmt.add(handleAmount), unusedAmt.subtract(handleAmount), contractNo, sourceAccount, creditAccount, contract, finance, WriteTargetTypeEnum.beforeCharge));
        /**  罚息核销 by ZC.GUO  **/
        handleAmount = handleAmount.add(this.normalPenaltyHandle(receiptOperateId, virtualAccountId, unusedId, receiptId, usedAmt.add(handleAmount), unusedAmt.subtract(handleAmount), contractNo, sourceAccount, creditAccount, contract, finance));
        /**  逾期90天以上的租金 by ZC.GUO  **/
        handleAmount = handleAmount.add(this.normalRepayPlanHandle(receiptOperateId, virtualAccountId, QueryPlanTypeEnum.gt, unusedId, receiptId, usedAmt.add(handleAmount), unusedAmt.subtract(handleAmount), contractNo, sourceAccount, creditAccount, contract, finance, RentTypeEnum.rent, WriteTargetTypeEnum.beforeCharge));
        /**  逾期90天以内（含）的租金核销 by ZC.GUO  **/
        handleAmount = handleAmount.add(this.normalRepayPlanHandle(receiptOperateId, virtualAccountId, QueryPlanTypeEnum.le, unusedId, receiptId, usedAmt.add(handleAmount), unusedAmt.subtract(handleAmount), contractNo, sourceAccount, creditAccount, contract, finance, RentTypeEnum.rent, WriteTargetTypeEnum.beforeCharge));
        /**  本期利息 by ZC.GUO  **/
        handleAmount = handleAmount.add(this.beforeChargeCurrentInterestHandle(receiptOperateId, virtualAccountId, receiptId, usedAmt.add(handleAmount), unusedAmt.subtract(handleAmount), contractNo, sourceAccount, creditAccount, contract, finance, manage));
        /**  本期本金 by ZC.GUO  **/
        handleAmount = handleAmount.add(this.normalRepayPlanHandle(receiptOperateId, virtualAccountId, QueryPlanTypeEnum.current, unusedId, receiptId, usedAmt.add(handleAmount), unusedAmt.subtract(handleAmount), contractNo, sourceAccount, creditAccount, contract, finance, RentTypeEnum.capital, WriteTargetTypeEnum.beforeCharge));
        /**  未到期本金 by ZC.GUO  **/
        handleAmount = handleAmount.add(this.normalRepayPlanHandle(receiptOperateId, virtualAccountId, QueryPlanTypeEnum.future, unusedId, receiptId, usedAmt.add(handleAmount), unusedAmt.subtract(handleAmount), contractNo, sourceAccount, creditAccount, contract, finance, RentTypeEnum.capital, WriteTargetTypeEnum.beforeCharge));
        /**
         * 保存凭证流水
         */
        this.insertVoucherFlow( contractNo, String.valueOf(receiptOperateId), VoucherBuriedPointNo.settlementAdvancePayment,businessType,  channelFullName,  batchDate,  account);

        /**  收据信息更新 by ZC.GUO  **/
        StringBuffer updateReceiptSql = new StringBuffer();
        updateReceiptSql.append("update basic_receipt_record set used_amt = ?,unused_amt = ?,account_id = ? where id = ?");
        PreparedStatement updateReceiptPs = null;
        try {
            updateReceiptPs = contract.prepareStatement(updateReceiptSql.toString());
            updateReceiptPs.setBigDecimal(1, usedAmt.add(handleAmount));
            updateReceiptPs.setBigDecimal(2, unusedAmt.subtract(handleAmount));
            updateReceiptPs.setLong(3, virtualAccountId);
            updateReceiptPs.setLong(4, receiptId);
            updateReceiptPs.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        } finally {
            clean(updateReceiptPs);
        }
        /**  账务处理 by ZC.GUO  **/
        Long custNo = this.getCustNoByVirtualId(finance, virtualAccountId);
        this.financeDetailHandle(sourceType, unusedId, receiptId, custNo, transLineId, receiptOperateId, unusedAmt, virtualAccountId, contractNo, handleAmount, finance);
    }


    /**
     * 正常核销
     *
     * @param
     * @return
     * @author ZC.GUO
     */
    public void normalReceiptHandle(String sourceType, Long virtualAccountId, Long unusedId, Long receiptId, BigDecimal usedAmt, BigDecimal unusedAmt, String contractNo, String sourceAccount, String creditAccount, Connection contract, Connection finance,Connection account, boolean isLpr, int overdueDays,Date  batchDate,String businessType,String channelFullName) {
        /**  业务操作id by ZC.GUO  **/
        Long transLineId = uidGenerator.getUID();
        /**  来款操作id by ZC.GUO  **/
        Long receiptOperateId = uidGenerator.getUID();
        /**  最终处理金额 by ZC.GUO  **/
        BigDecimal handleAmount = BigDecimal.ZERO;
        /**  费用核销（不包括提前结清费用） by ZC.GUO  **/
        handleAmount = handleAmount.add(this.normalCostHandle(receiptOperateId, virtualAccountId, unusedId, receiptId, usedAmt.add(handleAmount), unusedAmt.subtract(handleAmount), contractNo, sourceAccount, creditAccount, contract, finance, WriteTargetTypeEnum.normal));
        /**  罚息核销 by ZC.GUO  **/
        handleAmount = handleAmount.add(this.normalPenaltyHandle(receiptOperateId, virtualAccountId, unusedId, receiptId, usedAmt.add(handleAmount), unusedAmt.subtract(handleAmount), contractNo, sourceAccount, creditAccount, contract, finance));
        /**  逾期和当天应还 by ZC.GUO  **/
        /**  90天以下，时间久的优先，先利后本 by ZC.GUO  **/
        if (overdueDays <= 90) {
            handleAmount = handleAmount.add(this.normalRepayPlanHandle(receiptOperateId, virtualAccountId, QueryPlanTypeEnum.now, unusedId, receiptId, usedAmt.add(handleAmount), unusedAmt.subtract(handleAmount), contractNo, sourceAccount, creditAccount, contract, finance, RentTypeEnum.rent, WriteTargetTypeEnum.normal));
        }
        /**  90天以上，时间久的优先，先本后利 by ZC.GUO  **/
        else {
            handleAmount = handleAmount.add(this.normalRepayPlanHandle(receiptOperateId, virtualAccountId, QueryPlanTypeEnum.now, unusedId, receiptId, usedAmt.add(handleAmount), unusedAmt.subtract(handleAmount), contractNo, sourceAccount, creditAccount, contract, finance, RentTypeEnum.capital, WriteTargetTypeEnum.normal));
            handleAmount = handleAmount.add(this.normalRepayPlanHandle(receiptOperateId, virtualAccountId, QueryPlanTypeEnum.now, unusedId, receiptId, usedAmt.add(handleAmount), unusedAmt.subtract(handleAmount), contractNo, sourceAccount, creditAccount, contract, finance, RentTypeEnum.interest, WriteTargetTypeEnum.normal));

        }
        /**  未到期租金 by ZC.GUO  **/
        /**  非浮动利率 lpr判断  **/
        if (!isLpr) {
            handleAmount = handleAmount.add(this.normalRepayPlanHandle(receiptOperateId, virtualAccountId, QueryPlanTypeEnum.futureWithCurrent, unusedId, receiptId, usedAmt.add(handleAmount), unusedAmt.subtract(handleAmount), contractNo, sourceAccount, creditAccount, contract, finance, RentTypeEnum.rent, WriteTargetTypeEnum.normal));
        }
        /**
         * 保存凭证流水
         */
        this.insertVoucherFlow( contractNo, String.valueOf(receiptOperateId), VoucherBuriedPointNo.advancePaymentSettlementRent,businessType,  channelFullName,  batchDate,  account);

        /**  收据信息更新 by ZC.GUO  **/
        StringBuffer updateReceiptSql = new StringBuffer();
        updateReceiptSql.append("update basic_receipt_record set used_amt = ?,unused_amt = ?,account_id = ? where id = ?");
        PreparedStatement updateReceiptPs = null;
        try {
            updateReceiptPs = contract.prepareStatement(updateReceiptSql.toString());
            updateReceiptPs.setBigDecimal(1, usedAmt.add(handleAmount));
            updateReceiptPs.setBigDecimal(2, unusedAmt.subtract(handleAmount));
            updateReceiptPs.setLong(3, virtualAccountId);
            updateReceiptPs.setLong(4, receiptId);
            updateReceiptPs.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        } finally {
            clean(updateReceiptPs);
        }
        /**  账务处理 by ZC.GUO  **/
        Long custNo = this.getCustNoByVirtualId(finance, virtualAccountId);
        this.financeDetailHandle(sourceType, unusedId, receiptId, custNo, transLineId, receiptOperateId, unusedAmt, virtualAccountId, contractNo, handleAmount, finance);
    }

    public void financeDetailHandle(String sourceType, Long unusedId, Long receiptId, Long custNo, Long transLineId, Long receiptOperateId, BigDecimal unusedAmt, Long virtualAccountId, String contractNo, BigDecimal amount, Connection finance) {
        /**  虚拟账户更新 by ZC.GUO  **/
        StringBuffer updateAccountSql = new StringBuffer();
        updateAccountSql.append("update finance_virtual_account set used_amt  = used_amt + ?,un_used_amt = un_used_amt - ? where id= ?");
        PreparedStatement updateAccountPs = null;
        /**  账户余额分配流水 by ZC.GUO  **/
        StringBuffer insertUnusedFundsHisSql = new StringBuffer();
        insertUnusedFundsHisSql.append("insert into finance_unused_funds_history(id,account_id,contract_no,funds_type,fund_id,amount,create_by,create_time,del_flag) ");
        insertUnusedFundsHisSql.append(" values (?,?,?,'handle',?,?,'system',NOW(),0)");
        PreparedStatement insertUnusedFundsHisPs = null;
        /**  当前待分配款项信息更新或删除 by ZC.GUO  **/
        StringBuffer updateUnusedFundsSql = new StringBuffer();
        updateUnusedFundsSql.append("update finance_cust_unused_funds set amount  = ?,status = 'mayuse', revision = revision + 1 where id= ?");
        PreparedStatement updateUnusedFundsSqlPs = null;
        StringBuffer deleteUnusedFundsSql = new StringBuffer();
        deleteUnusedFundsSql.append("delete from finance_cust_unused_funds where id= ?");
        PreparedStatement deleteUnusedFundsPs = null;
        /** 业务操作记录 by ZC.GUO * */
        StringBuffer insertTransLineSql = new StringBuffer();
        insertTransLineSql.append("insert into finance_trans_line(id,cust_no,operate_type,operator,operate_time,operate_result,create_by,create_time) ");
        insertTransLineSql.append(" values (?,?,'distribution','system',NOW(),'success','system',NOW())");
        PreparedStatement insertTransLinePs = null;
        /** 来款操作记录 by ZC.GUO * */
        StringBuffer insertReceiptOperateSql = new StringBuffer();
        insertReceiptOperateSql.append("insert into finance_receipt_operate(id,trans_id,funds_type,funds_id,time,type,amount,create_by,create_time)");
        insertReceiptOperateSql.append(" values (?,?,?,?,NOW(),'distribution',?,'system',NOW())");
        PreparedStatement insertReceiptOperatePs = null;
        try {
            updateAccountPs = finance.prepareStatement(updateAccountSql.toString());
            updateAccountPs.setBigDecimal(1, amount);
            updateAccountPs.setBigDecimal(2, amount);
            updateAccountPs.setLong(3, virtualAccountId);
            updateAccountPs.executeUpdate();

            insertUnusedFundsHisPs = finance.prepareStatement(insertUnusedFundsHisSql.toString());
            insertUnusedFundsHisPs.setLong(1, uidGenerator.getUID());
            insertUnusedFundsHisPs.setLong(2, virtualAccountId);
            insertUnusedFundsHisPs.setString(3, contractNo);
            insertUnusedFundsHisPs.setLong(4, receiptId);
            insertUnusedFundsHisPs.setBigDecimal(5, amount);
            insertUnusedFundsHisPs.execute();
            /**  未全部分配 by ZC.GUO  **/
            if (unusedAmt.compareTo(amount) > 0) {
                updateUnusedFundsSqlPs = finance.prepareStatement(updateUnusedFundsSql.toString());
                updateUnusedFundsSqlPs.setBigDecimal(1, unusedAmt.subtract(amount));
                updateUnusedFundsSqlPs.setLong(2, unusedId);
                updateUnusedFundsSqlPs.executeUpdate();
            } else {
                deleteUnusedFundsPs = finance.prepareStatement(deleteUnusedFundsSql.toString());
                deleteUnusedFundsPs.setLong(1, unusedId);
                deleteUnusedFundsPs.execute();
            }
            insertTransLinePs = finance.prepareStatement(insertTransLineSql.toString());
            insertTransLinePs.setLong(1, transLineId);
            insertTransLinePs.setLong(2, custNo);
            insertTransLinePs.execute();
            insertReceiptOperatePs = finance.prepareStatement(insertReceiptOperateSql.toString());
            insertReceiptOperatePs.setLong(1, receiptOperateId);
            insertReceiptOperatePs.setLong(2, transLineId);
            insertReceiptOperatePs.setString(3, sourceType);
            insertReceiptOperatePs.setLong(4, receiptId);
            insertReceiptOperatePs.setBigDecimal(5, amount);
            insertReceiptOperatePs.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        } finally {
            clean(insertUnusedFundsHisPs);
            clean(updateUnusedFundsSqlPs);
            clean(deleteUnusedFundsPs);
            clean(insertTransLinePs);
            clean(insertReceiptOperatePs);
            clean(updateAccountPs);
        }
    }

    public BigDecimal beforeChargeCurrentInterestHandle(Long receiptOperateId, Long virtualAccountId, Long receiptId, BigDecimal usedAmt, BigDecimal unusedAmt, String contractNo, String sourceAccount, String creditAccount, Connection contract, Connection finance, Connection manage) {

        /**  应收利息查询 by ZC.GUO  **/
        StringBuffer queryInterestAmountSql = new StringBuffer();
        queryInterestAmountSql.append("SELECT IFNULL(qua.interest_receivable,0) amount");
        queryInterestAmountSql.append(" FROM manage_prepayment_apply app");
        queryInterestAmountSql.append(" LEFT JOIN manage_prepayment_quotation qua ON app.quotation_id = qua.id");
        queryInterestAmountSql.append(" WHERE app.apply_status = 'APPROVED' AND app.data_source = 'manyKinds'");
        queryInterestAmountSql.append(" AND app.contract_no = ?");

        PreparedStatement queryInterestAmountPs = null;
        ResultSet queryInterestAmountRs = null;


        /**  本期 by ZC.GUO  **/
        StringBuffer queryRepayPlanSql = new StringBuffer();
        queryRepayPlanSql.append("select temp.* from (");
        queryRepayPlanSql.append(" select @rownum:=@rownum+1 AS rownum, `status`,IFNULL(plan.actual_rent,0) actual_rent, IFNULL(plan.actual_principle,0) actual_principle, IFNULL(plan.actual_interest,0) actual_interest,plan.due_date,plan.id,plan.term_no,plan.receivable_rent,plan.receivable_principle,plan.receivable_interest");
        queryRepayPlanSql.append(" from (SELECT @rownum:=0) r, basic_repayment_plan plan");
        queryRepayPlanSql.append(" where plan.contract_no = ? and plan.`status` = 'UNPAID' and plan.del_flag = '0'");
        queryRepayPlanSql.append(" and plan.due_date >= date_sub(STR_TO_DATE(CONCAT(DATE_FORMAT(?,'%Y-%m-%d'),' 00:00:00'),'%Y-%m-%d %H:%i:%S'),interval 0 day)");
        queryRepayPlanSql.append(" order by plan.due_date asc");
        queryRepayPlanSql.append(" ) temp where temp.rownum = 1");

        BigDecimal amount = BigDecimal.ZERO;
        PreparedStatement queryRepayPlanPs = null;
        ResultSet queryRepayPlanRs = null;

        StringBuffer updateRepayPlanSql = new StringBuffer();
        updateRepayPlanSql.append("update basic_repayment_plan set actual_rent = ?,actual_interest = ?,actual_principle = ?,`status` = ?, actual_payment_date = ? where id = ?");
        PreparedStatement updateRepayPlanPs = null;
        try {
            queryInterestAmountPs = manage.prepareStatement(queryInterestAmountSql.toString());
            queryInterestAmountPs.setString(1, contractNo);
            queryInterestAmountRs = queryInterestAmountPs.executeQuery();
            if (EmptyUtils.isNotEmpty(queryInterestAmountRs) && queryInterestAmountRs.next()) {
                /**  应收利息不为空则进行处理 by ZC.GUO  **/
                BigDecimal actualReceivable = queryInterestAmountRs.getBigDecimal("amount");
                if (EmptyUtils.isNotEmpty(actualReceivable) && BigDecimal.ZERO.compareTo(actualReceivable) < 0) {
                    queryRepayPlanPs = contract.prepareStatement(queryRepayPlanSql.toString());
                    queryRepayPlanPs.setString(1, contractNo);
                    queryRepayPlanPs.setDate(2, new Date(batchDateInfo.getBatchDate().getTime()));
                    queryRepayPlanRs = queryRepayPlanPs.executeQuery();
                    while (EmptyUtils.isNotEmpty(queryRepayPlanRs) && queryRepayPlanRs.next() && unusedAmt.compareTo(BigDecimal.ZERO) > 0) {
                        /**  实收租金 by ZC.GUO  **/
                        BigDecimal actualRent = queryRepayPlanRs.getBigDecimal("actual_rent");
                        BigDecimal actualPrinciple = queryRepayPlanRs.getBigDecimal("actual_principle");
                        BigDecimal actualInterest = queryRepayPlanRs.getBigDecimal("actual_interest");
                        BigDecimal receivableRent = queryRepayPlanRs.getBigDecimal("receivable_rent");
                        BigDecimal receivablePrinciple = queryRepayPlanRs.getBigDecimal("receivable_principle");
                        /**  原始应收 by ZC.GUO  **/
                        BigDecimal receivableInterest = queryRepayPlanRs.getBigDecimal("receivable_interest");
                        /**  实际应收 by ZC.GUO  **/
                        Long id = queryRepayPlanRs.getLong("id");
                        Integer termNo = queryRepayPlanRs.getInt("term_no");
                        String status = queryRepayPlanRs.getString("status");
                        /**  利息核销金额 by ZC.GUO  **/
                        BigDecimal interestAmount = unusedAmt.compareTo(actualReceivable.subtract(actualInterest)) > 0 ? actualReceivable.subtract(actualInterest) : unusedAmt;
                        if (interestAmount.compareTo(BigDecimal.ZERO) > 0) {
                            actualInterest = actualInterest.add(interestAmount);
                            actualRent = actualRent.add(interestAmount);
                            updateRepayPlanPs = contract.prepareStatement(updateRepayPlanSql.toString());
                            updateRepayPlanPs.setBigDecimal(1, actualRent);
                            updateRepayPlanPs.setBigDecimal(2, actualInterest);
                            updateRepayPlanPs.setBigDecimal(3, actualPrinciple);
                            if (actualRent.compareTo(receivableRent) >= 0) {
                                updateRepayPlanPs.setString(4, RepaymentStatusEnum.REPAID.toString());
                                updateRepayPlanPs.setDate(5, new Date(batchDateInfo.getBatchDate().getTime()));
                            } else {
                                updateRepayPlanPs.setString(4, status);
                                updateRepayPlanPs.setNull(5, Types.DATE);
                            }
                            /**  如果本金已结清 by ZC.GUO  **/
                            if (receivablePrinciple.compareTo(actualPrinciple) <= 0) {
                                updateRepayPlanPs.setString(4, RepaymentStatusEnum.PREPAYMENT.toString());
                                updateRepayPlanPs.setDate(5, new Date(batchDateInfo.getBatchDate().getTime()));
                            }
                            updateRepayPlanPs.setLong(6, id);
                            updateRepayPlanPs.executeUpdate();
                            clean(updateRepayPlanPs);
                            /**  资产还款计划核销 by ZC.GUO  **/
                            this.handlePlanInterest(contractNo, termNo, interestAmount, receivableInterest, actualInterest, actualInterest, WriteTargetTypeEnum.beforeCharge, contract);
                            /**  未使用金额冲减 by ZC.GUO  **/
                            unusedAmt = unusedAmt.subtract(interestAmount);
                            /**  已使用金额增加 by ZC.GUO  **/
                            usedAmt = usedAmt.add(interestAmount);
                            /**  新增收据明细 by ZC.GUO  **/
                            Long receiptDetailId = this.addReceiptDetail(receiptId, FeeTypeEnum.interest.toString(), DealSourceEnum.repaymentPlan.toString(), "basic_repayment_plan", id, contractNo, termNo, interestAmount, true, contract);
                            /**  账务系统的相关处理 by ZC.GUO  **/
                            this.addOperateDetail(virtualAccountId, receiptOperateId, FeeTypeEnum.interest.toString(), "basic_repayment_plan", id, receiptDetailId, interestAmount, sourceAccount, creditAccount, finance);

                            /**  本费用核销完成后累加计算所有费用核销的总金额 by ZC.GUO  **/
                            amount = amount.add(interestAmount);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        } finally {
            clean(queryRepayPlanPs);
            clean(queryRepayPlanRs);
            clean(updateRepayPlanPs);
            clean(queryInterestAmountPs);
            clean(queryInterestAmountRs);
        }
        return amount;
    }

    public BigDecimal normalRepayPlanHandle(Long receiptOperateId, Long virtualAccountId, QueryPlanTypeEnum planType, Long unusedId, Long receiptId, BigDecimal usedAmt, BigDecimal unusedAmt, String contractNo, String sourceAccount, String creditAccount, Connection contract, Connection finance, RentTypeEnum rentType, WriteTargetTypeEnum writeTarget) {

        /**  逾期和当天（已处于期内且还款日为当天）应还的租金 by ZC.GUO  **/
        StringBuffer nowPlanSql = new StringBuffer();
        nowPlanSql.append("select `status`,IFNULL(plan.actual_rent,0) actual_rent, IFNULL(plan.actual_principle,0) actual_principle, IFNULL(plan.actual_interest,0) actual_interest,plan.due_date,plan.id,plan.term_no,plan.receivable_rent,plan.receivable_principle,plan.receivable_interest");
        nowPlanSql.append(" from basic_repayment_plan plan");
        nowPlanSql.append(" where plan.contract_no = ? and plan.`status` in ('OVERDUE','UNPAID') and plan.del_flag = '0'");
        nowPlanSql.append(" and plan.due_date <=  STR_TO_DATE( CONCAT( DATE_FORMAT(?, '%Y-%m-%d'), ' 23:59:59' ), '%Y-%m-%d %H:%i:%S' )");
        nowPlanSql.append(" order by plan.due_date asc");
        /**  逾期90天以内（含）的租金 by ZC.GUO  **/
        StringBuffer queryRepayPlanLe90Sql = new StringBuffer();
        queryRepayPlanLe90Sql.append("select `status`,IFNULL(plan.actual_rent,0) actual_rent, IFNULL(plan.actual_principle,0) actual_principle, IFNULL(plan.actual_interest,0) actual_interest,plan.due_date,plan.id,plan.term_no,plan.receivable_rent,plan.receivable_principle,plan.receivable_interest");
        queryRepayPlanLe90Sql.append(" from basic_repayment_plan plan");
        queryRepayPlanLe90Sql.append(" where plan.contract_no = ? and plan.`status` = 'OVERDUE' and plan.del_flag = '0'");
        queryRepayPlanLe90Sql.append(" and plan.due_date >= date_sub(STR_TO_DATE(CONCAT(DATE_FORMAT(?,'%Y-%m-%d'),' 23:59:59'),'%Y-%m-%d %H:%i:%S'),interval 90 day)");
        queryRepayPlanLe90Sql.append(" order by plan.due_date asc");
        /**  逾期90天以上的租金 by ZC.GUO  **/
        StringBuffer queryRepayPlanGt90Sql = new StringBuffer();
        queryRepayPlanGt90Sql.append("select `status`,IFNULL(plan.actual_rent,0) actual_rent, IFNULL(plan.actual_principle,0) actual_principle, IFNULL(plan.actual_interest,0) actual_interest,plan.due_date,plan.id,plan.term_no,plan.receivable_rent,plan.receivable_principle,plan.receivable_interest");
        queryRepayPlanGt90Sql.append(" from basic_repayment_plan plan");
        queryRepayPlanGt90Sql.append(" where plan.contract_no = ? and plan.`status` = 'OVERDUE' and plan.del_flag = '0'");
        queryRepayPlanGt90Sql.append(" and plan.due_date < date_sub(STR_TO_DATE(CONCAT(DATE_FORMAT(?,'%Y-%m-%d'),' 23:59:59'),'%Y-%m-%d %H:%i:%S'),interval 90 day)");
        queryRepayPlanGt90Sql.append(" order by plan.due_date asc");
        /**  本期（已处于期内） by ZC.GUO  **/
        StringBuffer queryRepayPlanCurrentSql = new StringBuffer();
        queryRepayPlanCurrentSql.append("select temp.* from (");
        queryRepayPlanCurrentSql.append(" select @rownum:=@rownum+1 AS rownum, `status`,IFNULL(plan.actual_rent,0) actual_rent, IFNULL(plan.actual_principle,0) actual_principle, IFNULL(plan.actual_interest,0) actual_interest,plan.due_date,plan.id,plan.term_no,plan.receivable_rent,plan.receivable_principle,plan.receivable_interest");
        queryRepayPlanCurrentSql.append(" from (SELECT @rownum:=0) r, basic_repayment_plan plan");
        queryRepayPlanCurrentSql.append(" where plan.contract_no = ? and plan.`status` = 'UNPAID' and plan.del_flag = '0'");
        queryRepayPlanCurrentSql.append(" and plan.due_date >= date_sub(STR_TO_DATE(CONCAT(DATE_FORMAT(?,'%Y-%m-%d'),' 00:00:00'),'%Y-%m-%d %H:%i:%S'),interval 0 day)");
        queryRepayPlanCurrentSql.append(" order by plan.due_date asc");
        queryRepayPlanCurrentSql.append(" ) temp where temp.rownum = 1");
        /**  未到期含当期（已处于期内但还没到还款日） by ZC.GUO  **/
        StringBuffer futureWithCurrentPlanSql = new StringBuffer();
        futureWithCurrentPlanSql.append("select temp.* from (");
        futureWithCurrentPlanSql.append(" select @rownum:=@rownum+1 AS rownum, `status`,IFNULL(plan.actual_rent,0) actual_rent, IFNULL(plan.actual_principle,0) actual_principle, IFNULL(plan.actual_interest,0) actual_interest,plan.due_date,plan.id,plan.term_no,plan.receivable_rent,plan.receivable_principle,plan.receivable_interest");
        futureWithCurrentPlanSql.append(" from (SELECT @rownum:=0) r, basic_repayment_plan plan");
        futureWithCurrentPlanSql.append(" where plan.contract_no = ? and plan.`status` = 'UNPAID' and plan.del_flag = '0'");
        futureWithCurrentPlanSql.append(" and plan.due_date > STR_TO_DATE(CONCAT(DATE_FORMAT(?,'%Y-%m-%d'),' 23:59:59'),'%Y-%m-%d %H:%i:%S')");
        futureWithCurrentPlanSql.append(" order by plan.due_date asc");
        futureWithCurrentPlanSql.append(" ) temp where temp.rownum > 0");
        /**  未到期（完全未处于期内） by ZC.GUO  **/
        StringBuffer queryRepayPlanFutureSql = new StringBuffer();
        queryRepayPlanFutureSql.append("select temp.* from (");
        queryRepayPlanFutureSql.append(" select @rownum:=@rownum+1 AS rownum, `status`,IFNULL(plan.actual_rent,0) actual_rent, IFNULL(plan.actual_principle,0) actual_principle, IFNULL(plan.actual_interest,0) actual_interest,plan.due_date,plan.id,plan.term_no,plan.receivable_rent,plan.receivable_principle,plan.receivable_interest");
        queryRepayPlanFutureSql.append(" from (SELECT @rownum:=0) r, basic_repayment_plan plan");
        queryRepayPlanFutureSql.append(" where plan.contract_no = ? and plan.`status` = 'UNPAID' and plan.del_flag = '0'");
        queryRepayPlanFutureSql.append(" and plan.due_date >= date_sub(STR_TO_DATE(CONCAT(DATE_FORMAT(?,'%Y-%m-%d'),' 00:00:00'),'%Y-%m-%d %H:%i:%S'),interval 0 day)");
        queryRepayPlanFutureSql.append(" order by plan.due_date asc");
        queryRepayPlanFutureSql.append(" ) temp where temp.rownum > 1");


        BigDecimal amount = BigDecimal.ZERO;
        StringBuffer queryRepayPlanSql = null;
        switch (planType) {
            case le:
                queryRepayPlanSql = queryRepayPlanLe90Sql;
                break;
            case now:
                queryRepayPlanSql = nowPlanSql;
                break;
            case gt:
                queryRepayPlanSql = queryRepayPlanGt90Sql;
                break;
            case current:
                queryRepayPlanSql = queryRepayPlanCurrentSql;
                break;
            case futureWithCurrent:
                queryRepayPlanSql = futureWithCurrentPlanSql;
                break;
            case future:
                queryRepayPlanSql = queryRepayPlanFutureSql;
                break;
        }
        PreparedStatement queryRepayPlanPs = null;
        ResultSet queryRepayPlanRs = null;

        StringBuffer updateRepayPlanSql = new StringBuffer();
        updateRepayPlanSql.append("update basic_repayment_plan set actual_rent = ?,actual_interest = ?,actual_principle = ?,`status` = ?, actual_payment_date = ? where id = ?");
        PreparedStatement updateRepayPlanPs = null;
        try {
            queryRepayPlanPs = contract.prepareStatement(queryRepayPlanSql.toString());
            queryRepayPlanPs.setString(1, contractNo);
            queryRepayPlanPs.setDate(2, new Date(batchDateInfo.getBatchDate().getTime()));
            queryRepayPlanRs = queryRepayPlanPs.executeQuery();
            while (EmptyUtils.isNotEmpty(queryRepayPlanRs) && queryRepayPlanRs.next() && unusedAmt.compareTo(BigDecimal.ZERO) > 0) {
                /**  实收租金 by ZC.GUO  **/
                BigDecimal actualRent = queryRepayPlanRs.getBigDecimal("actual_rent");
                BigDecimal actualPrinciple = queryRepayPlanRs.getBigDecimal("actual_principle");
                BigDecimal actualInterest = queryRepayPlanRs.getBigDecimal("actual_interest");
                BigDecimal receivableRent = queryRepayPlanRs.getBigDecimal("receivable_rent");
                BigDecimal receivablePrinciple = queryRepayPlanRs.getBigDecimal("receivable_principle");
                BigDecimal receivableInterest = queryRepayPlanRs.getBigDecimal("receivable_interest");
                Long id = queryRepayPlanRs.getLong("id");
                Integer termNo = queryRepayPlanRs.getInt("term_no");
                String status = queryRepayPlanRs.getString("status");
                /**  利息核销 by ZC.GUO  **/
                /**  利息核销金额 by ZC.GUO  **/
                BigDecimal interestAmount = unusedAmt.compareTo(receivableInterest.subtract(actualInterest)) > 0 ? receivableInterest.subtract(actualInterest) : unusedAmt;
                if (interestAmount.compareTo(BigDecimal.ZERO) > 0 && (RentTypeEnum.interest.equals(rentType) || RentTypeEnum.rent.equals(rentType))) {
                    actualInterest = actualInterest.add(interestAmount);
                    actualRent = actualRent.add(interestAmount);
                    updateRepayPlanPs = contract.prepareStatement(updateRepayPlanSql.toString());
                    updateRepayPlanPs.setBigDecimal(1, actualRent);
                    updateRepayPlanPs.setBigDecimal(2, actualInterest);
                    updateRepayPlanPs.setBigDecimal(3, actualPrinciple);
                    if (actualRent.compareTo(receivableRent) >= 0) {
                        updateRepayPlanPs.setString(4, RepaymentStatusEnum.REPAID.toString());
                    } else {
                        updateRepayPlanPs.setString(4, status);
                    }
                    /**  如果是提前还款并且本金已结清 by ZC.GUO  **/
                    if (WriteTargetTypeEnum.beforeCharge.equals(writeTarget) && receivablePrinciple.compareTo(actualPrinciple) <= 0) {
                        updateRepayPlanPs.setString(4, RepaymentStatusEnum.PREPAYMENT.toString());
                    }
                    updateRepayPlanPs.setDate(5, new Date(batchDateInfo.getBatchDate().getTime()));
                    updateRepayPlanPs.setLong(6, id);
                    updateRepayPlanPs.executeUpdate();
                    clean(updateRepayPlanPs);
                    /**  资产还款计划核销 by ZC.GUO  **/
                    this.handlePlanInterest(contractNo, termNo, interestAmount, receivableInterest, actualInterest, receivableInterest, writeTarget, contract);
                    /**  未使用金额冲减 by ZC.GUO  **/
                    unusedAmt = unusedAmt.subtract(interestAmount);
                    /**  已使用金额增加 by ZC.GUO  **/
                    usedAmt = usedAmt.add(interestAmount);
                    /**  新增收据明细 by ZC.GUO  **/
                    Long receiptDetailId = this.addReceiptDetail(receiptId, FeeTypeEnum.interest.toString(), DealSourceEnum.repaymentPlan.toString(), "basic_repayment_plan", id, contractNo, termNo, interestAmount, true, contract);
                    /**  账务系统的相关处理 by ZC.GUO  **/
                    this.addOperateDetail(virtualAccountId, receiptOperateId, FeeTypeEnum.interest.toString(), "basic_repayment_plan", id, receiptDetailId, interestAmount, sourceAccount, creditAccount, finance);

                    /**  本费用核销完成后累加计算所有费用核销的总金额 by ZC.GUO  **/
                    amount = amount.add(interestAmount);
                }
                /**  本金核销 by ZC.GUO  **/
                BigDecimal principleAmount = unusedAmt.compareTo(receivablePrinciple.subtract(actualPrinciple)) > 0 ? receivablePrinciple.subtract(actualPrinciple) : unusedAmt;
                if (principleAmount.compareTo(BigDecimal.ZERO) > 0 && (RentTypeEnum.capital.equals(rentType) || RentTypeEnum.rent.equals(rentType))) {
                    actualPrinciple = actualPrinciple.add(principleAmount);
                    actualRent = actualRent.add(principleAmount);
                    updateRepayPlanPs = contract.prepareStatement(updateRepayPlanSql.toString());
                    updateRepayPlanPs.setBigDecimal(1, actualRent);
                    updateRepayPlanPs.setBigDecimal(2, actualInterest);
                    updateRepayPlanPs.setBigDecimal(3, actualPrinciple);
                    if (actualRent.compareTo(receivableRent) >= 0) {
                        updateRepayPlanPs.setString(4, RepaymentStatusEnum.REPAID.toString());
                    } else {
                        updateRepayPlanPs.setString(4, status);
                    }
                    /**  如果是提前还款并且本金已结清 by ZC.GUO  **/
                    if (WriteTargetTypeEnum.beforeCharge.equals(writeTarget) && receivablePrinciple.compareTo(actualPrinciple) <= 0) {
                        updateRepayPlanPs.setString(4, RepaymentStatusEnum.PREPAYMENT.toString());
                    }
                    updateRepayPlanPs.setDate(5, new Date(batchDateInfo.getBatchDate().getTime()));
                    updateRepayPlanPs.setLong(6, id);
                    updateRepayPlanPs.executeUpdate();
                    clean(updateRepayPlanPs);
                    /**  资产还款计划核销 by ZC.GUO  **/
                    this.handlePlanPrinciple(contractNo, termNo, principleAmount, receivablePrinciple, actualPrinciple, writeTarget, contract);
                    /**  未使用金额冲减 by ZC.GUO  **/
                    unusedAmt = unusedAmt.subtract(principleAmount);
                    /**  已使用金额增加 by ZC.GUO  **/
                    usedAmt = usedAmt.add(principleAmount);
                    /**  新增收据明细 by ZC.GUO  **/
                    Long receiptDetailId = this.addReceiptDetail(receiptId, FeeTypeEnum.capital.toString(), DealSourceEnum.repaymentPlan.toString(), "basic_repayment_plan", id, contractNo, termNo, principleAmount, false, contract);
                    /**  账务系统的相关处理 by ZC.GUO  **/
                    this.addOperateDetail(virtualAccountId, receiptOperateId, FeeTypeEnum.capital.toString(), "basic_repayment_plan", id, receiptDetailId, principleAmount, sourceAccount, creditAccount, finance);
                    /**  本费用核销完成后累加计算所有费用核销的总金额 by ZC.GUO  **/
                    amount = amount.add(principleAmount);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        } finally {
            clean(queryRepayPlanPs);
            clean(queryRepayPlanRs);
            clean(updateRepayPlanPs);
        }
        return amount;
    }

    public void handlePlanInterest(String contractNo, Integer termNo, BigDecimal totalAmount, BigDecimal totalReceivableAmount, BigDecimal totalActualAmount, BigDecimal totalActualReceivable, WriteTargetTypeEnum writeTarget, Connection contract) {

        /**  资产还款计划表查询 by ZC.GUO  **/
        StringBuffer queryRepayPlanSql = new StringBuffer();
        queryRepayPlanSql.append("select plan.receivable_rent,plan.receivable_interest,plan.receivable_principle,IFNULL(plan.actual_interest,0) actual_interest,IFNULL(plan.actual_principle,0) actual_principle,IFNULL(plan.actual_rent,0) actual_rent,plan.`status`,plan.id");
        queryRepayPlanSql.append(" from basic_assets_repayment_plan plan");
        queryRepayPlanSql.append(" where plan.contract_no = ? and plan.term_no = ? and plan.`status` in ('UNPAID','OVERDUE') and plan.del_flag='0'");
        PreparedStatement queryRepayPlanPs = null;
        ResultSet queryRepayPlanRs = null;

        /**  需核销计划数量查询 by ZC.GUO  **/
        StringBuffer queryRepayPlanCountSql = new StringBuffer();
        queryRepayPlanCountSql.append("select count(plan.id) totalNum");
        queryRepayPlanCountSql.append(" from basic_assets_repayment_plan plan");
        queryRepayPlanCountSql.append(" where plan.contract_no = ? and plan.term_no = ? and plan.`status` in ('UNPAID','OVERDUE') and plan.del_flag='0'");
        PreparedStatement queryRepayPlanCountPs = null;
        ResultSet queryRepayPlanCountRs = null;

        /**  更新资产还款计划表信息 by ZC.GUO  **/
        StringBuffer updatePlanSql = new StringBuffer();
        updatePlanSql.append("update basic_assets_repayment_plan set actual_rent = ?,actual_interest = ?,`status` = ?, actual_date = ? where id = ?");
        PreparedStatement updatePlanPs = null;
        try {
            queryRepayPlanPs = contract.prepareStatement(queryRepayPlanSql.toString());
            queryRepayPlanPs.setString(1, contractNo);
            queryRepayPlanPs.setInt(2, termNo);
            queryRepayPlanRs = queryRepayPlanPs.executeQuery();

            queryRepayPlanCountPs = contract.prepareStatement(queryRepayPlanCountSql.toString());
            queryRepayPlanCountPs.setString(1, contractNo);
            queryRepayPlanCountPs.setInt(2, termNo);
            queryRepayPlanCountRs = queryRepayPlanCountPs.executeQuery();

            /**  资产还款计划表总条数 by ZC.GUO  **/
            int totalNum = 0;
            if (EmptyUtils.isNotEmpty(queryRepayPlanCountRs) && queryRepayPlanCountRs.next()) {
                totalNum = queryRepayPlanCountRs.getInt("totalNum");
            }

            /**  已核销金额 by ZC.GUO  **/
            BigDecimal usedAmt = BigDecimal.ZERO;

            int currentSeq = 1;
            while (EmptyUtils.isNotEmpty(queryRepayPlanRs) && queryRepayPlanRs.next() && totalAmount.compareTo(usedAmt) > 0) {
                /**  实收租金 by ZC.GUO  **/
                BigDecimal receivableRent = queryRepayPlanRs.getBigDecimal("receivable_rent");
                BigDecimal receivableInterest = queryRepayPlanRs.getBigDecimal("receivable_interest");
                BigDecimal receivablePrinciple = queryRepayPlanRs.getBigDecimal("receivable_principle");
                BigDecimal actualPrinciple = queryRepayPlanRs.getBigDecimal("actual_principle");
                BigDecimal actualInterest = queryRepayPlanRs.getBigDecimal("actual_interest");
                BigDecimal actualRent = queryRepayPlanRs.getBigDecimal("actual_rent");
                Long id = queryRepayPlanRs.getLong("id");
                String status = queryRepayPlanRs.getString("status");
                /**  本条计划还款计划本次应核销金额 by ZC.GUO  **/
                BigDecimal thisNeedAmt = BigDecimal.ZERO;
                BigDecimal thisAmt = BigDecimal.ZERO;
                /**  最后一条倒减 by ZC.GUO  **/
                if (currentSeq == totalNum && totalAmount.compareTo(usedAmt) > 0) {
                    thisNeedAmt = totalAmount.subtract(usedAmt);
                } else {
                    /**  本记录实应收利息 = 本记录本期应收*本合同本期实应收/本合同本期应收 by ZC.GUO  **/
                    BigDecimal actualReceivable = receivableInterest.multiply(totalActualReceivable.divide(totalReceivableAmount, 2, BigDecimal.ROUND_UP));
                    /**  本记录本次应收利息 = （本记录实应收利息*本合同本期总已收利息/本合同本期总应收利息）-本记录已收利息 by ZC.GUO  **/
                    thisNeedAmt = totalActualAmount.divide(totalReceivableAmount, 2, BigDecimal.ROUND_UP).multiply(actualReceivable).subtract(actualInterest).setScale(2, BigDecimal.ROUND_UP);
                    currentSeq++;
                }
                /**  最终核销金额 by ZC.GUO  **/
                thisAmt = thisNeedAmt.compareTo(totalAmount.subtract(usedAmt)) > 0 ? totalAmount.subtract(usedAmt) : thisNeedAmt;
                usedAmt = usedAmt.add(thisAmt);

                actualInterest = actualInterest.add(thisAmt);
                actualRent = actualRent.add(thisAmt);

                updatePlanPs = contract.prepareStatement(updatePlanSql.toString());
                updatePlanPs.setBigDecimal(1, actualRent);
                updatePlanPs.setBigDecimal(2, actualInterest);

                if (receivableRent.compareTo(actualRent) > 0) {
                    updatePlanPs.setString(3, status);
                } else {
                    updatePlanPs.setString(3, RepaymentStatusEnum.REPAID.toString());
                }
                /**  如果是提前还款并且本金已结清 by ZC.GUO  **/
                if (WriteTargetTypeEnum.beforeCharge.equals(writeTarget) && receivablePrinciple.compareTo(actualPrinciple) <= 0) {
                    updatePlanPs.setString(3, RepaymentStatusEnum.PREPAYMENT.toString());
                }
                updatePlanPs.setDate(4, new Date(batchDateInfo.getBatchDate().getTime()));
                updatePlanPs.setLong(5, id);
                updatePlanPs.executeUpdate();
                clean(updatePlanPs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        } finally {
            clean(updatePlanPs);
            clean(queryRepayPlanPs);
            clean(queryRepayPlanRs);
            clean(queryRepayPlanCountPs);
            clean(queryRepayPlanCountRs);
        }
    }

    public void handlePlanPrinciple(String contractNo, Integer termNo, BigDecimal totalAmount, BigDecimal totalReceivableAmount, BigDecimal totalActualAmount, WriteTargetTypeEnum writeTarget, Connection contract) {

        /**  资产还款计划表查询 by ZC.GUO  **/
        StringBuffer queryRepayPlanSql = new StringBuffer();
        queryRepayPlanSql.append("select plan.receivable_rent,plan.receivable_interest,plan.receivable_principle,IFNULL(plan.actual_interest,0) actual_interest,IFNULL(plan.actual_principle,0) actual_principle,IFNULL(plan.actual_rent,0) actual_rent,plan.`status`,plan.id");
        queryRepayPlanSql.append(" from basic_assets_repayment_plan plan");
        queryRepayPlanSql.append(" where plan.contract_no = ? and plan.term_no = ? and plan.`status` in ('UNPAID','OVERDUE')");
        PreparedStatement queryRepayPlanPs = null;
        ResultSet queryRepayPlanRs = null;

        /**  需核销计划数量查询 by ZC.GUO  **/
        StringBuffer queryRepayPlanCountSql = new StringBuffer();
        queryRepayPlanCountSql.append("select count(plan.id) totalNum");
        queryRepayPlanCountSql.append(" from basic_assets_repayment_plan plan");
        queryRepayPlanCountSql.append(" where plan.contract_no = ? and plan.term_no = ? and plan.`status` in ('UNPAID','OVERDUE')");
        PreparedStatement queryRepayPlanCountPs = null;
        ResultSet queryRepayPlanCountRs = null;

        /**  更新资产还款计划表信息 by ZC.GUO  **/
        StringBuffer updatePlanSql = new StringBuffer();
        updatePlanSql.append("update basic_assets_repayment_plan set actual_rent = ?,actual_principle = ?,`status` = ?, actual_date = ? where id = ?");
        PreparedStatement updatePlanPs = null;
        try {
            queryRepayPlanPs = contract.prepareStatement(queryRepayPlanSql.toString());
            queryRepayPlanPs.setString(1, contractNo);
            queryRepayPlanPs.setInt(2, termNo);
            queryRepayPlanRs = queryRepayPlanPs.executeQuery();

            queryRepayPlanCountPs = contract.prepareStatement(queryRepayPlanCountSql.toString());
            queryRepayPlanCountPs.setString(1, contractNo);
            queryRepayPlanCountPs.setInt(2, termNo);
            queryRepayPlanCountRs = queryRepayPlanCountPs.executeQuery();

            /**  资产还款计划表总条数 by ZC.GUO  **/
            int totalNum = 0;
            if (EmptyUtils.isNotEmpty(queryRepayPlanCountRs) && queryRepayPlanCountRs.next()) {
                totalNum = queryRepayPlanCountRs.getInt("totalNum");
            }
            /**  已核销金额 by ZC.GUO  **/
            BigDecimal usedAmt = BigDecimal.ZERO;

            int currentSeq = 1;
            while (EmptyUtils.isNotEmpty(queryRepayPlanRs) && queryRepayPlanRs.next() && totalAmount.compareTo(usedAmt) > 0) {
                /**  实收租金 by ZC.GUO  **/
                BigDecimal receivableRent = queryRepayPlanRs.getBigDecimal("receivable_rent");
                BigDecimal receivablePrinciple = queryRepayPlanRs.getBigDecimal("receivable_principle");
                BigDecimal actualPrinciple = queryRepayPlanRs.getBigDecimal("actual_principle");
                BigDecimal actualRent = queryRepayPlanRs.getBigDecimal("actual_rent");
                Long id = queryRepayPlanRs.getLong("id");
                String status = queryRepayPlanRs.getString("status");
                /**  本条计划还款计划本次应核销金额 by ZC.GUO  **/
                BigDecimal thisNeedAmt = BigDecimal.ZERO;
                BigDecimal thisAmt = BigDecimal.ZERO;
                /**  最后一条倒减 by ZC.GUO  **/
                if (currentSeq == totalNum && totalAmount.compareTo(usedAmt) > 0) {
                    thisNeedAmt = totalAmount.subtract(usedAmt);
                } else {
                    /**  本记录本次应收本金 = （本记录应收本金*本合同本期总应收本金/本合同本期已收本金）-本记录已收本金 by ZC.GUO  **/
                    thisNeedAmt = totalActualAmount.divide(totalReceivableAmount, 2, BigDecimal.ROUND_UP).multiply(receivablePrinciple).subtract(actualPrinciple).setScale(2, BigDecimal.ROUND_UP);
                    currentSeq++;
                }
                /**  最终核销金额 by ZC.GUO  **/
                thisAmt = thisNeedAmt.compareTo(totalAmount.subtract(usedAmt)) > 0 ? totalAmount.subtract(usedAmt) : thisNeedAmt;
                usedAmt = usedAmt.add(thisAmt);

                actualPrinciple = actualPrinciple.add(thisAmt);
                actualRent = actualRent.add(thisAmt);

                updatePlanPs = contract.prepareStatement(updatePlanSql.toString());
                updatePlanPs.setBigDecimal(1, actualRent);
                updatePlanPs.setBigDecimal(2, actualPrinciple);

                if (receivableRent.compareTo(actualRent) > 0) {
                    updatePlanPs.setString(3, status);
                } else {
                    updatePlanPs.setString(3, RepaymentStatusEnum.REPAID.toString());
                }
                /**  如果是提前还款并且本金已结清 by ZC.GUO  **/
                if (WriteTargetTypeEnum.beforeCharge.equals(writeTarget) && receivablePrinciple.compareTo(actualPrinciple) <= 0) {
                    updatePlanPs.setString(3, RepaymentStatusEnum.PREPAYMENT.toString());
                }
                updatePlanPs.setDate(4, new Date(batchDateInfo.getBatchDate().getTime()));
                updatePlanPs.setLong(5, id);
                updatePlanPs.executeUpdate();
                clean(updatePlanPs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        } finally {
            clean(queryRepayPlanPs);
            clean(queryRepayPlanRs);
            clean(updatePlanPs);
            clean(queryRepayPlanCountPs);
            clean(queryRepayPlanCountRs);
        }
    }


    public BigDecimal normalPenaltyHandle(Long receiptOperateId, Long virtualAccountId, Long unusedId, Long receiptId, BigDecimal usedAmt, BigDecimal unusedAmt, String contractNo, String sourceAccount, String creditAccount, Connection contract, Connection finance) {
        BigDecimal amount = BigDecimal.ZERO;

        /**  获取待核销的罚息记录信息 by ZC.GUO  **/
        StringBuffer queryExpenseRecordSql = new StringBuffer();
        queryExpenseRecordSql.append("select exp.id,exp.remaining_amt,exp.cost_type");
        queryExpenseRecordSql.append(" from basic_expense_record exp");
        queryExpenseRecordSql.append(" where exp.`status` = 'unsettle' and exp.remaining_amt > 0 and exp.cost_type = 'penalty' and exp.contract_no = ? and exp.del_flag = '0'");
        PreparedStatement queryExpenseRecordPs = null;
        ResultSet queryExpenseRecordRs = null;


        /**  更新待核销费用信息 by ZC.GUO  **/
        StringBuffer updateExpenseRecordSql = new StringBuffer();
        updateExpenseRecordSql.append("update basic_expense_record set received_amt = received_amt + ?,remaining_amt = remaining_amt - ?,`status` = ?  where id = ?");
        PreparedStatement updateExpenseDetailPs = null;
        try {
            queryExpenseRecordPs = contract.prepareStatement(queryExpenseRecordSql.toString());
            queryExpenseRecordPs.setString(1, contractNo);
            queryExpenseRecordRs = queryExpenseRecordPs.executeQuery();
            while (EmptyUtils.isNotEmpty(queryExpenseRecordRs) && queryExpenseRecordRs.next() && unusedAmt.compareTo(BigDecimal.ZERO) > 0) {
                /**  罚息id by ZC.GUO  **/
                Long expenseId = queryExpenseRecordRs.getLong("id");
                /**  罚息金额 by ZC.GUO  **/
                BigDecimal remainingAmt = queryExpenseRecordRs.getBigDecimal("remaining_amt");
                /**  费用类型（罚息） by ZC.GUO  **/
                String costType = queryExpenseRecordRs.getString("cost_type");
                /**  当前罚息实际核销金额 by ZC.GUO  **/
                BigDecimal thisAmount = unusedAmt.compareTo(remainingAmt) > 0 ? remainingAmt : unusedAmt;
                /**  本罚息记录明细核销 by ZC.GUO  **/
                this.handlePenaltyDetail(contractNo, thisAmount, costType, contract);
                /**  本罚息记录核销 by ZC.GUO  **/
                updateExpenseDetailPs = contract.prepareStatement(updateExpenseRecordSql.toString());
                updateExpenseDetailPs.setBigDecimal(1, thisAmount);
                updateExpenseDetailPs.setBigDecimal(2, thisAmount);
                updateExpenseDetailPs.setString(3, remainingAmt.compareTo(thisAmount) > 0 ? ExpenseStatusEnum.unsettle.toString() : ExpenseStatusEnum.settle.toString());
                updateExpenseDetailPs.setLong(4, expenseId);
                updateExpenseDetailPs.executeUpdate();
                clean(updateExpenseDetailPs);
                /**  未使用金额冲减 by ZC.GUO  **/
                unusedAmt = unusedAmt.subtract(thisAmount);
                /**  已使用金额增加 by ZC.GUO  **/
                usedAmt = usedAmt.add(thisAmount);
                /**  新增收据明细 by ZC.GUO  **/
                Long receiptDetailId = this.addReceiptDetail(receiptId, costType, DealSourceEnum.penaltyInterest.toString(), "basic_expense_record", expenseId, contractNo, null, thisAmount, true, contract);
                /**  账务系统的相关处理 by ZC.GUO  **/
                this.addOperateDetail(virtualAccountId, receiptOperateId, costType, "basic_expense_record", expenseId, receiptDetailId, thisAmount, sourceAccount, creditAccount, finance);
                /**  本费用核销完成后累加计算所有费用核销的总金额 by ZC.GUO  **/
                amount = amount.add(thisAmount);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        } finally {
            clean(queryExpenseRecordPs);
            clean(queryExpenseRecordRs);
            clean(updateExpenseDetailPs);
        }

        return amount;
    }

    /**
     * 新增收据处理明细
     *
     * @param
     * @return
     * @author ZC.GUO
     */
    public Long addReceiptDetail(Long receiptId, String receiptType, String dealSource, String dealTable, Long dealId, String contractNo, Integer termNo, BigDecimal dealAmt, boolean needTax, Connection contract) {
        Long receiptDetailId = uidGenerator.getUID();
        StringBuffer insertReceiptDetailSql = new StringBuffer();
        insertReceiptDetailSql.append("insert into basic_receipt_deal_details");
        insertReceiptDetailSql.append(" (id,receipt_id,deal_type,receipt_type,deal_source,deal_table,deal_id,contract_no,term_no,deal_amt,tax,`status`,revision,create_by,create_time,del_flag)");
        insertReceiptDetailSql.append("VALUES(?,?,'unassignedAmt',?,?,?,?,?,?,?,?,'creditSuccess',0,'system',NOW(),'0')");
        PreparedStatement insertReceiptDetailPs = null;
        try {
            insertReceiptDetailPs = contract.prepareStatement(insertReceiptDetailSql.toString());
            insertReceiptDetailPs.setLong(1, receiptDetailId);
            insertReceiptDetailPs.setLong(2, receiptId);
            insertReceiptDetailPs.setString(3, receiptType);
            insertReceiptDetailPs.setString(4, dealSource);
            insertReceiptDetailPs.setString(5, dealTable);
            insertReceiptDetailPs.setLong(6, dealId);
            insertReceiptDetailPs.setString(7, contractNo);
            if (EmptyUtils.isNotEmpty(termNo)) {
                insertReceiptDetailPs.setInt(8, termNo);
            } else {
                insertReceiptDetailPs.setNull(8, Types.INTEGER);
            }
            insertReceiptDetailPs.setBigDecimal(9, dealAmt);
            if (needTax) {
                BigDecimal tax = dealAmt.divide(BigDecimal.ONE.add(new BigDecimal("0.06")), 2, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal("0.06")).setScale(2, BigDecimal.ROUND_HALF_UP);
                insertReceiptDetailPs.setBigDecimal(10, tax);
            } else {
                insertReceiptDetailPs.setNull(10, Types.DECIMAL);
            }
            insertReceiptDetailPs.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        } finally {
            clean(insertReceiptDetailPs);
        }
        return receiptDetailId;
    }

    public BigDecimal normalCostHandle(Long receiptOperateId, Long virtualAccountId, Long unusedId, Long receiptId, BigDecimal usedAmt, BigDecimal unusedAmt, String contractNo, String sourceAccount, String creditAccount, Connection contract, Connection finance, WriteTargetTypeEnum writeTarget) {
        BigDecimal amount = BigDecimal.ZERO;

        /**  获取待核销的费用记录信息 by ZC.GUO  **/
        StringBuffer queryExpenseRecordSql = new StringBuffer();
        queryExpenseRecordSql.append("select exp.id,exp.remaining_amt,exp.cost_type");
        queryExpenseRecordSql.append(" from basic_expense_record exp");
        queryExpenseRecordSql.append(" where exp.`status` = 'unsettle' and exp.remaining_amt > 0 ");
        /**  如果是提前还款 by ZC.GUO  **/
        if (WriteTargetTypeEnum.beforeCharge.equals(writeTarget)) {
            queryExpenseRecordSql.append(" and exp.cost_type in ('urge','changeCost','other','advanceSettle') ");
        } else {
            queryExpenseRecordSql.append(" and exp.cost_type in ('urge','changeCost','other') ");
        }
        queryExpenseRecordSql.append(" and exp.contract_no = ? and exp.del_flag = '0'");
        PreparedStatement queryExpenseRecordPs = null;
        ResultSet queryExpenseRecordRs = null;


        /**  更新待核销费用信息 by ZC.GUO  **/
        StringBuffer updateExpenseRecordSql = new StringBuffer();
        updateExpenseRecordSql.append("update basic_expense_record set received_amt = received_amt + ?,remaining_amt = remaining_amt - ?,`status` = ?  where id = ?");
        PreparedStatement updateExpenseDetailPs = null;
        try {
            queryExpenseRecordPs = contract.prepareStatement(queryExpenseRecordSql.toString());
            queryExpenseRecordPs.setString(1, contractNo);
            queryExpenseRecordRs = queryExpenseRecordPs.executeQuery();
            while (EmptyUtils.isNotEmpty(queryExpenseRecordRs) && queryExpenseRecordRs.next() && unusedAmt.compareTo(BigDecimal.ZERO) > 0) {
                /**  费用id by ZC.GUO  **/
                Long expenseId = queryExpenseRecordRs.getLong("id");
                /**  费用金额 by ZC.GUO  **/
                BigDecimal remainingAmt = queryExpenseRecordRs.getBigDecimal("remaining_amt");
                /**  费用类型 by ZC.GUO  **/
                String costType = queryExpenseRecordRs.getString("cost_type");
                /**  当前费用实际核销金额 by ZC.GUO  **/
                BigDecimal thisAmount = unusedAmt.compareTo(remainingAmt) > 0 ? remainingAmt : unusedAmt;
                /**  本费用记录明细核销 by ZC.GUO  **/
                this.handleCostDetail(contractNo, thisAmount, costType, contract);
                /**  本费用记录核销 by ZC.GUO  **/
                updateExpenseDetailPs = contract.prepareStatement(updateExpenseRecordSql.toString());
                updateExpenseDetailPs.setBigDecimal(1, thisAmount);
                updateExpenseDetailPs.setBigDecimal(2, thisAmount);
                updateExpenseDetailPs.setString(3, remainingAmt.compareTo(thisAmount) > 0 ? ExpenseStatusEnum.unsettle.toString() : ExpenseStatusEnum.settle.toString());
                updateExpenseDetailPs.setLong(4, expenseId);
                updateExpenseDetailPs.executeUpdate();
                clean(updateExpenseDetailPs);
                /**  未使用金额冲减 by ZC.GUO  **/
                unusedAmt = unusedAmt.subtract(thisAmount);
                /**  已使用金额增加 by ZC.GUO  **/
                usedAmt = usedAmt.add(thisAmount);
                /**  新增收据明细 by ZC.GUO  **/
                Long receiptDetailId = this.addReceiptDetail(receiptId, costType, DealSourceEnum.cost.toString(), "basic_expense_record", expenseId, contractNo, null, thisAmount, true, contract);
                /**  账务系统的相关处理 by ZC.GUO  **/
                this.addOperateDetail(virtualAccountId, receiptOperateId, costType, "basic_expense_record", expenseId, receiptDetailId, thisAmount, sourceAccount, creditAccount, finance);
                /**  本费用核销完成后累加计算所有费用核销的总金额 by ZC.GUO  **/
                amount = amount.add(thisAmount);
              }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        } finally {
            clean(queryExpenseRecordPs);
            clean(queryExpenseRecordRs);
            clean(updateExpenseDetailPs);
        }

        return amount;
    }

    public void addOperateDetail(Long virtualAccountId, Long receiptOperateId, String feeType, String feeSourceTable, Long feeId, Long receiptDetailId, BigDecimal amount, String payAccount, String receiptAccount, Connection finance) {
        StringBuffer insertOperateDetailSql = new StringBuffer();
        insertOperateDetailSql.append("insert into finance_operate_detail ");
        insertOperateDetailSql.append(" (id,account_id,operate_id,fee_type,fee_source_table,fee_id,receipt_detail_id,amount,pay_account,receipt_account,create_by,create_time)");
        insertOperateDetailSql.append(" VALUES(?,?,?,?,?,?,?,?,?,?,'system',NOW())");
        PreparedStatement insertOperateDetailPs = null;
        try {
            insertOperateDetailPs = finance.prepareStatement(insertOperateDetailSql.toString());
            insertOperateDetailPs.setLong(1, uidGenerator.getUID());
            insertOperateDetailPs.setLong(2, virtualAccountId);
            insertOperateDetailPs.setLong(3, receiptOperateId);
            insertOperateDetailPs.setString(4, feeType);
            insertOperateDetailPs.setString(5, feeSourceTable);
            insertOperateDetailPs.setLong(6, feeId);
            insertOperateDetailPs.setLong(7, receiptDetailId);
            insertOperateDetailPs.setBigDecimal(8, amount);
            insertOperateDetailPs.setString(9, payAccount);
            insertOperateDetailPs.setString(10, receiptAccount);
            insertOperateDetailPs.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        } finally {
            clean(insertOperateDetailPs);
        }
    }

    public void handleCostDetail(String contractNo, BigDecimal totalAmt, String costType, Connection contract) {
        /**  获取待核销的费用明细记录信息 by ZC.GUO  **/
        StringBuffer queryExpenseDetailSql = new StringBuffer();
        queryExpenseDetailSql.append("select det.id,det.remaining_amt");
        queryExpenseDetailSql.append(" from basic_expense_record_detail det ");
        queryExpenseDetailSql.append(" where det.contract_no = ? and det.`status` = 'unsettle' and det.cost_type = ? and det.del_flag = '0'");
        PreparedStatement queryExpenseDetailPs = null;
        ResultSet queryExpenseDetailRs = null;


        /**  更新待核销费用明细信息 by ZC.GUO  **/
        StringBuffer updateExpenseDetailSql = new StringBuffer();
        updateExpenseDetailSql.append("update basic_expense_record_detail set received_amt = received_amt + ?,remaining_amt = remaining_amt - ?,`status` = ? where id = ?");
        PreparedStatement updateExpenseDetailPs = null;

        /**  新增流水记录 by ZC.GUO  **/
        StringBuffer insertExpenseLineSql = new StringBuffer();
        insertExpenseLineSql.append("insert into basic_penalty_interest_details");
        insertExpenseLineSql.append(" (id,contract_no,term_no,cost_type,operate_type,date,amount,create_by,create_time,del_flag)");
        insertExpenseLineSql.append(" VALUES(?,?,null,?,?,NOW(),?,'system',NOW(),'0')");
        PreparedStatement insertExpenseLinePs = null;
        try {
            queryExpenseDetailPs = contract.prepareStatement(queryExpenseDetailSql.toString());
            queryExpenseDetailPs.setString(1, contractNo);
            queryExpenseDetailPs.setString(2, costType);
            queryExpenseDetailRs = queryExpenseDetailPs.executeQuery();
            while (EmptyUtils.isNotEmpty(queryExpenseDetailRs) && queryExpenseDetailRs.next() && totalAmt.compareTo(BigDecimal.ZERO) > 0) {
                /**  费用明细id by ZC.GUO  **/
                Long detailId = queryExpenseDetailRs.getLong("id");
                /**  费用明细金额 by ZC.GUO  **/
                BigDecimal remainingAmt = queryExpenseDetailRs.getBigDecimal("remaining_amt");
                /**  当前费用明细实际核销金额 by ZC.GUO  **/
                BigDecimal thisAmount = totalAmt.compareTo(remainingAmt) > 0 ? remainingAmt : totalAmt;
                /**  更新待核销费用明细信息 by ZC.GUO  **/
                updateExpenseDetailPs = contract.prepareStatement(updateExpenseDetailSql.toString());
                updateExpenseDetailPs.setBigDecimal(1, thisAmount);
                updateExpenseDetailPs.setBigDecimal(2, thisAmount);
                updateExpenseDetailPs.setString(3, remainingAmt.compareTo(thisAmount) > 0 ? ExpenseStatusEnum.unsettle.toString() : ExpenseStatusEnum.settle.toString());
                updateExpenseDetailPs.setLong(4, detailId);
                int num = updateExpenseDetailPs.executeUpdate();
                clean(updateExpenseDetailPs);
                /**  新增费用流水记录 by ZC.GUO  **/
                insertExpenseLinePs = contract.prepareStatement(insertExpenseLineSql.toString());
                insertExpenseLinePs.setLong(1, uidGenerator.getUID());
                insertExpenseLinePs.setString(2, contractNo);
                insertExpenseLinePs.setString(3, costType);
                insertExpenseLinePs.setString(4, CostDetailedTypeEnum.arrearsEntry.toString());
                insertExpenseLinePs.setBigDecimal(5, thisAmount);
                insertExpenseLinePs.execute();
                clean(insertExpenseLinePs);
                /**  本费用核销完成后减去相应的金额 by ZC.GUO  **/
                totalAmt = totalAmt.subtract(thisAmount);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        } finally {
            clean(queryExpenseDetailPs);
            clean(queryExpenseDetailRs);
            clean(updateExpenseDetailPs);
            clean(insertExpenseLinePs);
        }
    }

    public void handlePenaltyDetail(String contractNo, BigDecimal totalAmt, String costType, Connection contract) {
        /**  获取待核销的罚息明细记录信息 by ZC.GUO  **/
        StringBuffer queryPenaltyDetailSql = new StringBuffer();
        queryPenaltyDetailSql.append("select pen.id,pen.total_penalty,pen.received_amt,pen.exempted_amt,pen.term_no");
        queryPenaltyDetailSql.append(" from basic_penalty_interest pen");
        queryPenaltyDetailSql.append(" where pen.`status` = 'unsettle' and pen.contract_no = ? and pen.del_flag = '0'");
        queryPenaltyDetailSql.append(" order by pen.start_date asc");
        PreparedStatement queryPenaltyDetailPs = null;
        ResultSet queryPenaltyDetailRs = null;


        /**  更新待核销费用明细信息 by ZC.GUO  **/
        StringBuffer updatePenaltyDetailSql = new StringBuffer();
        updatePenaltyDetailSql.append("update basic_penalty_interest set received_amt = ?,`status` = ?,`completion_date` = ? where id = ?");
        PreparedStatement updatePenaltyDetailPs = null;

        /**  新增流水记录 by ZC.GUO  **/
        StringBuffer insertExpenseLineSql = new StringBuffer();
        insertExpenseLineSql.append("insert into basic_penalty_interest_details");
        insertExpenseLineSql.append(" (id,contract_no,term_no,cost_type,operate_type,date,amount,create_by,create_time,del_flag)");
        insertExpenseLineSql.append(" VALUES(?,?,?,?,?,NOW(),?,'system',NOW(),0)");
        PreparedStatement insertExpenseLinePs = null;
        try {
            queryPenaltyDetailPs = contract.prepareStatement(queryPenaltyDetailSql.toString());
            queryPenaltyDetailPs.setString(1, contractNo);
            queryPenaltyDetailRs = queryPenaltyDetailPs.executeQuery();
            while (EmptyUtils.isNotEmpty(queryPenaltyDetailRs) && queryPenaltyDetailRs.next() && totalAmt.compareTo(BigDecimal.ZERO) > 0) {
                /**  罚息明细id by ZC.GUO  **/
                Long detailId = queryPenaltyDetailRs.getLong("id");
                /**  罚息总应收总额 by ZC.GUO  **/
                BigDecimal totalPenalty = queryPenaltyDetailRs.getBigDecimal("total_penalty");
                /**  罚息已收总额 by ZC.GUO  **/
                BigDecimal receivedAmt = queryPenaltyDetailRs.getBigDecimal("received_amt");
                /**  罚息已豁免总额 by ZC.GUO  **/
                BigDecimal exemptedAmt = queryPenaltyDetailRs.getBigDecimal("exempted_amt");
                /**  期数 by ZC.GUO  **/
                Integer termNo = queryPenaltyDetailRs.getInt("term_no");
                /**  剩余应收发虚 by ZC.GUO  **/
                BigDecimal remainingAmt = totalPenalty.subtract(receivedAmt).subtract(exemptedAmt);
                /**  罚息尚欠款进行处理 by ZC.GUO  **/
                if (remainingAmt.compareTo(BigDecimal.ZERO) > 0) {
                    /**  当前罚息明细实际核销金额 by ZC.GUO  **/
                    BigDecimal thisAmount = totalAmt.compareTo(remainingAmt) > 0 ? remainingAmt : totalAmt;
                    receivedAmt = receivedAmt.add(thisAmount);

                    boolean flag = totalPenalty.subtract(receivedAmt).subtract(exemptedAmt).compareTo(BigDecimal.ZERO) > 0;
                    /**  更新待核销罚息明细信息 by ZC.GUO  **/
                    updatePenaltyDetailPs = contract.prepareStatement(updatePenaltyDetailSql.toString());
                    updatePenaltyDetailPs.setBigDecimal(1, receivedAmt);
                    if (flag) {
                        /**  未还清 by ZC.GUO  **/
                        updatePenaltyDetailPs.setString(2, ExpenseStatusEnum.unsettle.toString());
                        updatePenaltyDetailPs.setNull(3, Types.DATE);
                    } else {
                        /**  已还清 by ZC.GUO  **/
                        updatePenaltyDetailPs.setString(2, ExpenseStatusEnum.settle.toString());
                        updatePenaltyDetailPs.setDate(3, new Date(batchDateInfo.getBatchDate().getTime()));
                    }
                    updatePenaltyDetailPs.setLong(4, detailId);
                    updatePenaltyDetailPs.executeUpdate();
                    clean(updatePenaltyDetailPs);
                    /**  新增费用流水记录 by ZC.GUO  **/
                    insertExpenseLinePs = contract.prepareStatement(insertExpenseLineSql.toString());
                    insertExpenseLinePs.setLong(1, uidGenerator.getUID());
                    insertExpenseLinePs.setString(2, contractNo);
                    insertExpenseLinePs.setInt(3, termNo);
                    insertExpenseLinePs.setString(4, costType);
                    insertExpenseLinePs.setString(5, CostDetailedTypeEnum.arrearsEntry.toString());
                    insertExpenseLinePs.setBigDecimal(6, thisAmount);
                    insertExpenseLinePs.execute();
                    clean(insertExpenseLinePs);
                    /**  本罚息核销完成后减去相应的金额 by ZC.GUO  **/
                    totalAmt = totalAmt.subtract(thisAmount);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        } finally {
            clean(queryPenaltyDetailPs);
            clean(queryPenaltyDetailRs);
            clean(updatePenaltyDetailPs);
            clean(insertExpenseLinePs);
        }
    }


    /**
     * 未分配金额记录没有对应的收据，冲减响应金额并删除相应记录
     *
     * @param
     * @return
     * @author ZC.GUO
     */
    public void takeFinanceForReceiptNotFind(Long virtualAccountId, Long unusedId, BigDecimal amount, Connection finance) {
        /**  更新账户信息 by ZC.GUO  **/
        StringBuffer updateSql = new StringBuffer();
        updateSql.append("update finance_virtual_account set used_amt = used_amt + ? , un_used_amt = un_used_amt - ? where id = ?");
        /**  删除未分配金额记录 by ZC.GUO  **/
        StringBuffer deleteSql = new StringBuffer();
        deleteSql.append("delete from finance_cust_unused_funds where id = ?");
        PreparedStatement updatePs = null;
        PreparedStatement deletePs = null;
        try {
            updatePs = finance.prepareStatement(updateSql.toString());
            updatePs.setBigDecimal(1, amount);
            updatePs.setBigDecimal(2, amount);
            updatePs.setLong(3, virtualAccountId);
            updatePs.executeUpdate();

            deletePs = finance.prepareStatement(deleteSql.toString());
            deletePs.setLong(1, unusedId);
            deletePs.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        } finally {
            clean(updatePs);
            clean(deletePs);
        }
    }

    /**
     * 查询需要进行未分配月处理的虚拟账户id
     *
     * @param
     * @return
     * @author ZC.GUO
     */
    public List<Long> getVirtualAccountId(Connection finance, String breakPointString) {
        /**  断点 by ZC.GUO  **/
        JSONObject breakPoint = null;
        if (EmptyUtils.isNotEmpty(breakPointString)) {
            breakPoint = JSONObject.parseObject(breakPointString);
        }

        List<Long> idList = new ArrayList<>();
        StringBuffer querySql = new StringBuffer();
        querySql.append("select id from finance_virtual_account acc where acc.un_used_amt > 0 and acc.del_flag = '0'");

        /**  添加断点 by ZC.GUO  **/
        if (EmptyUtils.isNotEmpty(breakPoint)) {
            String virtualAccountId = breakPoint.getString("virtualAccountId");
            querySql.append(" and acc.id > " + virtualAccountId);
        }

        querySql.append(" order by acc.id asc ");
        PreparedStatement queryPs = null;
        ResultSet queryRs = null;
        try {
            queryPs = finance.prepareStatement(querySql.toString());
            queryRs = queryPs.executeQuery();
            while (EmptyUtils.isNotEmpty(queryRs) && queryRs.next()) {
                Long virtualAccountId = queryRs.getLong("id");
                if (EmptyUtils.isNotEmpty(virtualAccountId)) {
                    idList.add(virtualAccountId);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        } finally {
            clean(queryPs);
            clean(queryRs);
        }
        return idList;
    }

    public Long getCustNoByVirtualId(Connection finance, Long id) {
        Long custNo = null;
        StringBuffer querySql = new StringBuffer();
        querySql.append("select cust_no from finance_virtual_account acc where acc.id = ? and acc.un_used_amt > 0 and acc.del_flag = '0'");
        PreparedStatement queryPs = null;
        ResultSet queryRs = null;
        try {
            queryPs = finance.prepareStatement(querySql.toString());
            queryPs.setLong(1, id);
            queryRs = queryPs.executeQuery();
            if (EmptyUtils.isNotEmpty(queryRs) && queryRs.next()) {
                /**  正在扣款中指令数量 by ZC.GUO  **/
                custNo = queryRs.getLong("cust_no");
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        } finally {
            clean(queryPs);
            clean(queryRs);
        }
        return custNo;
    }

    /**
     * 保存凭证流水
     * @param contractNo
     */
    public void insertVoucherFlow(String contractNo, String transNo, String buriedPointNo,String businessype,String dealerName,Date bathDate,Connection contractAcount) {

        StringBuffer saveFlow = new StringBuffer();
        saveFlow.append("insert into voucher_flow_info(id,trans_no,buried_point_no,contract_no,status,dealer_name ,business_type,keep_account_date,create_by,create_time ) ");
        saveFlow.append("values(?,?,?,?,?,?,?,?,?,?)");
        PreparedStatement psFlow = null;
        try {
            psFlow = contractAcount.prepareStatement(saveFlow.toString());
            psFlow.setLong(1, uidGenerator.getUID());
            psFlow.setString(2, transNo);
            psFlow.setString(3, buriedPointNo);
            psFlow.setString(4, contractNo);
            psFlow.setString(5, AfsEnumUtil.key(VoucherStatusEnum.NOT_CREATED));
            psFlow.setString(6, dealerName);
            psFlow.setString(7, businessype);
            psFlow.setDate(8, bathDate);
            psFlow.setString(9, "system");
            psFlow.setDate(10, bathDate);
            psFlow.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        } finally {
            clean(psFlow);
        }
    }

}
